﻿## Generic report template
## Scheduler Usage: powershell -command "& {C:\WINDOWS\Scripts\Report-Template.ps1 -SendEmail}"
## Command Line Usage (no email): .\Report-Template.ps1
## Command Line Usage (default email): .\Report-Template.ps1 -SendEmail
## Command Line Usage (one-off email): .\Report-Template.ps1 -SendEmail name@domain.com
##

param ([switch]$SendEmail , [string]$OneOffEmailAddr = "default")
# By default do not send email.
cls
$DebugPreference = "SilentlyContinue"  #This is default
#$DebugPreference = "Continue"  #This causes more output
#$DebugPreference = "Inquire"  #This causes output and queries to continue or stop
#$DebugPreference = "Stop"  #This causes output and stops


## This script produces a ______ for ________.  
## David Ray - mm/dd/yyyy
Write-Debug "Starting Report Script"
Write-Debug "Setting configuration variables"

## Setup Variables ######
#Enter addresses in array form. i.e. @("name1@dom1.com","name2@dom2.com") if none use @("")
#$defaultTo = ("name1@dom.tld","name2@dom.tld")
#$defaultCc = ("copy1@dom.tld","copy2@dom.tld")
#$defaultBcc = ("bcc1@dom.tld","bcc2@dom.tld")
$defaultTo = "david_f_ray@dell.com"
## from should be "Exchange@ps.net" (not in Array form)
$defaultFrom = "ExchangeOp@ps.net"
#the mail subject 
$subject = "CHW Monthly Test Report"
#The body of the message
$emailMessage = "Test Report – Showing cool test stuff"
#relay server
$SMTPserver = "smtp-relay.chw.edu"
$reportName = "TestReport" + $(Get-Date -Format "yyMMdd")
$reportPath = "E:\Reports\TestReport"
## End of Setup Variables ######

#Load up the Report Library Functions.  ReportLibrary.ps1 must be in the same dir as the report script
Write-Debug "Load Report Libary Functions"
#. ((Split-Path -Parent (Resolve-Path -Path $MyInvocation.InvocationName)) + "\ReportLibrary.ps1")
$ScriptName = $MyInvocation.MyCommand.Name
$ScriptDir = Split-Path $MyInvocation.MyCommand.Path
. "$ScriptDir\ReportLibrary.ps1"


#######
## Here is the main data gathering portion of the script
Write-Debug "Running command to get data"
$GetVariable = Get-User  -ResultSize unlimited david.ray.002 ## Get the basic information

$ReportVariable = @() ## Create empty array for result

## Loop through basic info populating ReportObject
Write-Debug "Entering loop to build data list"
foreach ($thing in $GetVariable) {
	$UserReport = New-Object PSObject ## Make blank object for report element
	
	$UserReport | Add-Member -MemberType NoteProperty -Name DisplayName -Value $thing.DisplayName
	$UserReport | Add-Member -MemberType NoteProperty -Name WindowsEmailAddress -Value $thing.WindowsEmailAddress
	$UserReport | Add-Member -MemberType NoteProperty -Name RecipientType -Value $thing.RecipientType
	
	$ReportVariable += $UserReport
}
Write-Debug "Exiting data building loop"
## Output CSV of the information
Write-Debug "Saving CSV to $($reportPath)\$($reportName).csv"
$ReportVariable | Export-Csv -NoTypeInformation "$($reportPath)\$($reportName).csv"

## Save all this out to Excel and prettify it some.

## Excel constants that will help us
Write-Debug "Call Excel Var Function"
Set-ExcelVars

# Create an instance of Excel
Write-Debug "Call function to open Excel with CSV"
Open-ExcelCSV $reportPath $reportName

# Add a little formatting to the column header
Write-Debug "Call function to format header"
Set-ExcelHeaderFormat

## Examples of conditional formatting

##$bucket= $Worksheet.Range("H:H").FormatConditions.Add($xlCellValue, $xlGreaterEqual, 60)
##$Worksheet.Range("H:H").FormatConditions.Item(1).Interior.Color = 13551615
##$Worksheet.Range("H:H").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
##$Worksheet.Range("H:H").FormatConditions.Item(1).Interior.TintAndShade = 0
##$Worksheet.Range("H:H").FormatConditions.Item(1).Font.Color = -16383844
##$Worksheet.Range("H:H").FormatConditions.Item(1).Font.TintAndShade = 0

##$bucket= $Worksheet.Range("L:L").FormatConditions.Add($xlCellValue, $xlEqual, '="No"')
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.Color = 13551615
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.TintAndShade = 0
##$Worksheet.Range("L:L").FormatConditions.Item(1).Font.Color = -16383844
##$Worksheet.Range("L:L").FormatConditions.Item(1).Font.TintAndShade = 0

##$bucket = $Worksheet.Range("L:L").FormatConditions.Add($xlCellValue, $xlEqual, '="Yes"')
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.Color = 13561798
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.PatternColorIndex = $xlAutomatic
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.TintAndShade = 0
##$Worksheet.Range("L:L").FormatConditions.Item(2).Font.Color = -16752384
##$Worksheet.Range("L:L").FormatConditions.Item(2).Font.TintAndShade = 0

# Make Columns wide enough for content
Write-Debug "Calling function to autofit columns"
Set-ExcelAutofitColumns

#Save the Excel File. Use format $xlOpenXMLWorkbook for normal xlsx and $xlExcel12 for xlsb
Write-Debug "Calling function to save file"
Save-Excel $reportPath $reportName $xlExcel12
#Save-Excel makes available $reportFullName for sending

## Send Mail
if ($sendEmail.IsPresent) {
	Write-Debug "Calling send email function"
	Send-Email $OneOffEmailAddr $defaultTo $defaultCc $defaultBcc $defaultFrom `
					$subject $emailbody $reportFullName $SMTPserver
}
Write-Debug "End of Report script"